<?php
class BusinessModel extends model
{
	static $_instance = null;

	static function getInstance ()
	{
		if (is_null(self::$_instance)) {
			self::$_instance = new self();
		}
		return self::$_instance;
	}
	
	/**
	 * 取得总件数
	 * Enter description here ...
	 */
	function getCount($p){
		$sql = "select count(*) as c from crm_business_view where stop=0 and return_flg=0 ";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 记录列表
	 * Enter description here ...
	 */
	function getDispatchingList($p){
		$sql = "select * from crm_business_view where stop=0 and return_flg=0  ";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 取得施工部门总件数
	 * Enter description here ...
	 */
	function getConCount($p){
		$sql = "select count(*) as c from crm_business_view where stop=0 and return_flg=0 and statuss>=3 and statuss<=7";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 施工部门记录列表
	 * Enter description here ...
	 */
	function getConDispatchingList($p){
		$sql = "select * from crm_business_view where stop=0 and return_flg=0 and statuss>=3 and statuss<=7";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 施工审核
	 * Enter description here ...
	 */
	function getReviewCount($p){
		$sql = "select count(*) as c from crm_business_view where stop=0 and return_flg=0 and statuss=8 ";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 施工审核列表
	 * Enter description here ...
	 */
	function getDispatchingReview($p){
		$sql = "select * from crm_business_view where stop=0 and return_flg=0 and statuss=8 ";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 取得总件数
	 * Enter description here ...
	 */
	function getBakCount($p){
		$sql = "select count(*) as c from crm_businessbak_view where stop=0 and return_flg=0 ";
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		//if($p["dis_agency_id"] != "0"){$sql .= " and dis_agency_id in (".$p["dis_agency_id"].")";}
		//if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 记录列表
	 * Enter description here ...
	 */
	function getDispatchingBakList($p){
		$sql = "select * from crm_businessbak_view where stop=0 and return_flg=0  ";
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		//if($p["dis_agency_id"] != "0"){$sql .= " and dis_agency_id in (".$p["dis_agency_id"].")";}
		//if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 被驳回总件数
	 * Enter description here ...
	 */
	function getReturnCount($p){
		$sql = "select count(*) as c from crm_business_view where stop=0 and return_flg=1 ";
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		//if($p["dis_agency_id"] != "0"){$sql .= " and dis_agency_id in (".$p["dis_agency_id"].")";}
		//if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 被驳回记录列表
	 * Enter description here ...
	 */
	function getReturnList($p){
		$sql = "select * from crm_business_view where stop=0 and return_flg=1 ";
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		//if($p["dis_agency_id"] != "0"){$sql .= " and dis_agency_id in (".$p["dis_agency_id"].")";}
		//if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 被驳回总件数
	 * Enter description here ...
	 */
	function getStopCount($p){
		$sql = "select count(*) as c from crm_business_view where stop=1 ";
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		//if($p["dis_agency_id"] != "0"){$sql .= " and dis_agency_id in (".$p["dis_agency_id"].")";}
		//if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 被驳回记录列表
	 * Enter description here ...
	 */
	function getStopList($p){
		$sql = "select * from crm_business_view where stop=1 ";
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		//if($p["dis_agency_id"] != "0"){$sql .= " and dis_agency_id in (".$p["dis_agency_id"].")";}
		//if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		if($p["dis_agency_id"] != "0" && $p["con_agency_id"] != "0"){
			$sql .= " and (dis_agency_id in (".$p["dis_agency_id"].") or con_agency_id in (".$p["con_agency_id"]."))";
		}
		if($p["created_start"] != ""){$sql .= " and created>='".$p["created_start"]."'";}
		if($p["created_end"] != ""){$sql .= " and created<='".$p["created_end"]."'";}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["order_no"] != "0"){$sql .= " and id ='".$p["order_no"]."'";}
		if($p["statuss"] != "0"){$sql .= " and statuss='".$p["statuss"]."'";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 工单详情
	 * Enter description here ...
	 */
	function getBusinessInfo($p){
		return $this->sql("select * from crm_business_view where id=[id]",$p)->s(1);
	}
	
	/**
	 * 工单详情
	 * Enter description here ...
	 */
	function getBusinessInfo2($p){
		return $this->sql("select * from crm_businessbak_view where id=[id]",$p)->s(1);
	}
	
	/**
	 * 工单作废
	 * Enter description here ...
	 */
	function ToStop($p){
		return $this->sql("update crm_business set stop=[stop] where id=[id]",$p)->u();
	}
	
	/**
	 * 设置派工部门信息
	 * Enter description here ...
	 * @param unknown_type $p
	 */
	function BusinessAgency($p){
		$sql  = "update crm_business  ";
		$sql .= "set `dis_agency_id`='[dis_agency_id]',`dis_staff_id`='[dis_staff_id]', ";
		$sql .= "    `plan_start`='[plan_start]',`plan_end`='[plan_end]', ";
		$sql .= "    `con_agency_id`='[con_agency_id]',`plan_work_time`='[plan_work_time]', ";
		$sql .= "    `remark`='[remark]',`statuss`=2 ";
		$sql .= "where id=[id] ";
		$this->sql($sql,$p)->u();
	}
	
	/**
	 * 设置派工员工
	 * Enter description here ...
	 * @param unknown_type $p
	 */
	function BusinessStaff($p){
		$sql  = "update crm_business  ";
		$sql .= "set `con_staff_id`='[con_staff_id]', ";
		$sql .= "    `remark`='[remark]',`statuss`=3 ";
		$sql .= "where id=[id] ";
		$this->sql($sql,$p)->u();
	}
	
	/**
	 * 设置派工驳回
	 * Enter description here ...
	 * @param unknown_type $p
	 */
	function BusinessReturn($p){
		$sql  = "update crm_business  ";
		$sql .= "set `return_staff`='[return_staff]',`return_content`='[return_content]', ";
		$sql .= "    `return_flg`=1,`return_time`=now() ";
		$sql .= "where id=[id] ";
		$this->sql($sql,$p)->u();
	}
	
	/**
	 * 完工工单转移
	 * Enter description here ...
	 * @param unknown_type $p
	 */
	function RemoveReturn(){
		$this->sql("insert into crm_business_bak (select * from crm_business where statuss=9)")->e();
		$this->sql("delete from crm_business where statuss=9")->e();
	}
	
	function StaffBybusiness($p){
		$bus = $this->sql("select group_concat(business) as business from crm_agency where FIND_IN_SET('[cid]',contact)",$p)->s(1);
		return $bus["business"];
	}
	
	function ReturnOk($p){
		$this->sql("update crm_business set return_flg=0,statuss=if((statuss - 1)>=1,(statuss - 1),1) where id=[id] ",$p)->u();
	}
	
	function ReturnNg($p){
		$this->sql("update crm_business set return_flg=0 where id=[id] ",$p)->u();
	}
	
	/**
	 * 取得进度总件数
	 * Enter description here ...
	 */
	function getScheduleCount($p){
		$sql = "select count(*) as c from crm_business_view where stop=0 and statuss>=3 and statuss<=7 and return_flg=0 ";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 记录进度列表
	 * Enter description here ...
	 */
	function getScheduleList($p){
		$sqltemp ="select cbv.*,ifnull(csv.schedule,0) as schedule from crm_business_view as cbv left join crm_schedule_view as csv on cbv.id=csv.business_id";
		$sql = "select * from (".$sqltemp.")a where stop=0 and statuss>=3 and statuss<=7 and return_flg=0  ";
		if($p["id"] != "0"){$sql .= " and id=".$p["id"];}
		if($p["order_id"] != ""){$sql .= " and order_id ='".$p["order_id"]."'";}
		if($p["types"] != "0"){$sql .= " and types=".$p["types"];}
		if($p["con_agency_id"] != "0"){$sql .= " and con_agency_id in (".$p["con_agency_id"].")";}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	/**
	 * 记录进度列表详情
	 * Enter description here ...
	 */
	function getScheduleInfoCount($p){
		$sql = "select cws.*,cs.name as staff from crm_work_schedule as cws left join crm_staff as cs on cws.staff_id=cs.id where cws.stop=0 ";
		if($p["id"] != "0"){$sql .= " and cws.business_id=".$p["id"];}
		$return = $this->sql($sql)->s(1);
		return $return["c"];
	}
	
	/**
	 * 记录进度列表详情
	 * Enter description here ...
	 */
	function getScheduleInfoList($p){
		$sql = "select cws.*,cs.name as staff from crm_work_schedule as cws left join crm_staff as cs on cws.staff_id=cs.id where cws.stop=0 ";
		if($p["id"] != "0"){$sql .= " and cws.business_id=".$p["id"];}
		$sql .= " order by ".$p["sortField"]." ".$p["sortOrder"];
		$sql .= " limit [start],[end]";

		return $this->sql($sql,array("start"=>$p["r"]*$p["p"],"end"=>$p["r"]))->s();
	}
	
	

	/**
	 * 记录进度记录保存
	 * Enter description here ...
	 */
	function BusinessScheduleEdit($p,$agency_array){
		if($p["schedule"]==100){
			$this->sql("update crm_business set statuss = 8 where id=[business_id] ",$p)->u();
			//$agency = CommonModel::getInstance()->getAgencyAllList();
			//$agency_array = ArrayTool::ArrayToParentIDs($agency,$p["con_agency"]);
			$this->sql("delete from crm_business_review where `business_id`=".$p[business_id])->d();
			foreach ($agency_array as $key=>$item){
				$sql = "insert into crm_business_review(`business_id`,`agency_id`)values('".$p[business_id]."',$item)";
				$this->sql($sql)->i();
			}
		}
		return $this->sql("insert into crm_work_schedule(`business_id`,`staff_id`,`schedule`,`content`)values([business_id],[staff_id],[schedule],'[content]')",$p)->i();
	}
	
	function BusinessOkList($id){
		$sql = "select cbr.*,ccs.staff,ccs.contact as staff_id,ca.name as agencyname ";
        $sql.= "from crm_business_review as cbr left join crm_contact_view ccs on cbr.agency_id=ccs.id ";
        $sql.= "                                left join crm_agency ca on cbr.agency_id=ca.id ";
        $sql.= "where cbr.business_id =$id ";
        $sql.= "order by cbr.id desc ";
		return $this->sql($sql)->s();
	}
	
	function ReviewUpd($p,$business_id){
		$this->sql("update crm_business_review set `staff`='[staff]',`status`='[status]',`remark`='[remark]' where id=[id] ",$p)->u();
		$rs = $this->sql("select count(id) as con from crm_business_review where (`status`='未通过' or `status`='未审核') and business_id=$business_id ")->s(1);
		if($rs["con"] == 0){
			$this->sql("update crm_business set `statuss`='9' where id=$business_id")->u();
			return true;
		}else{
			$this->sql("update crm_business_review set `staff`='[staff]',`status`='[status]',`remark`='[remark]' where id=[id] ",$p)->u();
			return false;
		}
	}
	
	function DeclareSave($p){
		return $this->sql("insert into crm_business(`types`,`customer_id`,`title`,`content`,`business_staff_id`,`business_created`,`created`)values('[types]','[customs_id]','[title]','[content]','[business_staff_id]',now(),now())",$p)->i();
	}
	
	function ContactView($p){
		$rs = $this->sql("select group_concat(contact) as contact from crm_contact_view where id in ($p)")->s(1);
		return $rs["contact"];
	}
	
	function ReviewBusiness($p){
		$rs = $this->sql("select business_id from crm_business_review where id=[id] ",$p)->s(1);
		return $rs["business_id"];
	}
	
	function SetOrder($p){
		return $this->sql("update crm_business set order_id = '[order_id]' where id='[id]'",$p)->u();
	}
	
	function getCustomerByID($p){
		$re = $this->sql("select customer_id from crm_business where id='[id]'",$p)->s(1);
		if($re){
			return $re['customer_id'];
		}else{
			return '';
		}
	}

    public function add($data)
    {
        $sql = insertSql('crm_business', $data);
        return $this->sql($sql)->i();
    }
}